﻿
-- =============================================
-- Author:		EricHu
-- Create date: 2012年03月08日
-- Description:	2012年03月08日编码规范化
-- =============================================
CREATE PROCEDURE [dbo].[GetRecordByPage] 
	@TableName          VARCHAR(4000),           -- 表名
    @SelectField        VARCHAR(4000),           -- 要显示的字段名(不要加select)
    @WhereConditional   VARCHAR(4000),           -- 查询条件(注意: 不要加 where)
    @SortExpression     VARCHAR(255),            -- 排序索引字段名
    @PageSize           INT = 20,                -- 页大小
    @PageIndex          INT = 1,                 -- 页码
    @RecordCount        INT OUTPUT,              -- 返回记录总数
    @SortDire           VARCHAR(5) = 'DESC'      -- 设置排序类型
AS
BEGIN

	DECLARE @commandText VARCHAR(8000)      -- 主语句
	DECLARE @TopN INT 				        -- 获取前几条记录
	DECLARE @PageCount INT 				    -- 总共会是几页
	DECLARE @TopLimit INT 				    -- 获取多少条记录
	DECLARE @SQLRowCount NVARCHAR(4000)     -- 用于查询记录总数的语句
	DECLARE @SQLOrder VARCHAR(400)          -- 排序类型
	DECLARE @SQLTemp VARCHAR(4000)          -- 临时变量

	SET @SortExpression = LTRIM(RTRIM(@SortExpression))
	SET @SortDire = UPPER(LTRIM(RTRIM(@SortDire)))
	
	-- 这里是计算整体记录行数
	IF @RecordCount IS NULL
	BEGIN
		IF @WhereConditional != ''
		BEGIN
		  SET @SQLRowCount = 'SELECT @RecordCount=COUNT(1) FROM ' + @TableName + ' WHERE ' + @WhereConditional
		END
		ELSE
		BEGIN
		  SET @SQLRowCount = 'SELECT @RecordCount=COUNT(1) FROM ' + @TableName
		END
	END

	-- SELECT @RecordCount=@@ROWCOUNT
	EXEC sp_executesql @SQLRowCount, N'@RecordCount INT OUT', @RecordCount out

	IF @RecordCount IS NULL
	BEGIN
	   SET @RecordCount = 0
	END
	
	-- 这里是控制页数最多少
	SET @PageCount = @RecordCount / @PageSize + 1
	
	-- 这里检查当前页的有效性
	IF (@PageIndex < 1)
	BEGIN
		SET @PageIndex = 1
	END
	
	-- 这里限制最后一页的有效性
	IF (@PageIndex > @PageCount)
	BEGIN
		SET @PageIndex = @PageCount
	END

	IF @SortDire != 'ASC'
	BEGIN
		SET @SQLTemp = '<(SELECT MIN'
		SET @SQLOrder = ' ORDER BY ' + @SortExpression + ' DESC'
	END
	ELSE
	BEGIN
		set @SQLTemp = '>(SELECT MAX'
		set @SQLOrder = ' ORDER BY ' + @SortExpression + ' ASC'
	END
	
	-- 这里是调试信息
    -- SELECT @SQLOrder

	-- 获取几条数据？ EricHu 2010-11-02 更新
	SET @TopN = @RecordCount - @PageSize * (@PageIndex - 1)
	IF @TopN > @PageSize
	BEGIN
		SET @TopN = @PageSize
	END

	SET @TopLimit = @PageSize * (@PageIndex - 1)
	IF @TopLimit > @RecordCount
	BEGIN
		SET @TopLimit = @RecordCount
	END

	SET @commandText = 'SELECT TOP ' + STR(@TopN) + ' ' + @SelectField + ' FROM '
		+ @TableName + ' WHERE ' + @SortExpression + @SQLTemp + '('
		+ RIGHT(@SortExpression, LEN(@SortExpression) - CHARINDEX('.', @SortExpression)) + ') FROM (SELECT TOP ' + STR(@TopLimit)
		+ ' ' + @SortExpression + ' FROM ' + @TableName  + @SQLOrder + ') AS TableTemp)'
		+ @SQLOrder

	IF @WhereConditional != ''
		SET @commandText = 'SELECT TOP ' + STR(@TopN) + ' ' + @SelectField + ' FROM '
			+ @TableName + ' WHERE ' + @SortExpression + @SQLTemp + '('
			+ RIGHT(@SortExpression, LEN(@SortExpression) - CHARINDEX('.',@SortExpression)) + ') FROM (SELECT TOP ' + STR(@TopLimit)
			+ ' ' + @SortExpression + ' FROM ' + @TableName + ' WHERE ' + @WhereConditional + ' '
			+ @SQLOrder + ') AS TableTemp) AND ' + @WhereConditional + ' ' + @SQLOrder

	IF @PageIndex = 1
	BEGIN
		-- 第一页的显示效率提高
		SET @SQLTemp = ''
		IF @WhereConditional != ''
			SET @SQLTemp = ' WHERE ' + @WhereConditional

		SET @commandText = 'SELECT TOP ' + STR(@TopN) + ' ' + @SelectField 
					      + ' FROM ' + @TableName + @SQLTemp + ' ' + @SQLOrder
	END
	ELSE
	BEGIN	
		-- 解决大数据最有一页卡死的问题
		IF @PageIndex = @PageCount
		BEGIN
			IF @SortDire = 'ASC'
			BEGIN
				SET @SQLOrder = ' ORDER BY ' + @SortExpression + ' DESC'
			END
			ELSE
			BEGIN
				SET @SQLOrder = ' ORDER BY ' + @SortExpression + ' ASC'
			END
		
			SET @SQLTemp = ''
			IF @WhereConditional != ''
				SET @SQLTemp = ' WHERE ' + @WhereConditional
				
			SET @commandText = 'SELECT TOP ' + STR(@TopN) + ' ' + @SelectField 
							  + ' FROM ' + @TableName + @SQLTemp + ' ' + @SQLOrder
		    
			SET @commandText = 'SELECT ' + @SelectField
							  + ' FROM (' + @commandText + ') AS TableTemp ORDER BY ' + @SortExpression + ' ' + @SortDire
		END
	END
	
	EXEC (@commandText)
	
	-- 这个是调试程序用的
	-- SELECT @commandText
	
END

